package limousine.dbservice;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import javax.sql.DataSource;
import limousine.util.Func;
import limousine.vo.Image;



public class ImageService {

    private DataSource ds = null;
    private Func func = new Func();

    public ImageService() {
        //ds = (new Service()).getDataSource();
    }

    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

////////////////////////////////////////////GET
    public Image getById(String id) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("select * from image where id = '" + id.trim() + "' ");
        Image o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
            }
            rs.close();
            stmt.close();
            conn.close();            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }
    public Image getLastByUpdateBy(String updateBy) {//have one
        StringBuffer sql = new StringBuffer();
        sql.append("select * from image where update_by = '" + updateBy.trim() + "' order by create_date desc limit 1 ");
        Image o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            if (rs.next()) {
                o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }
    public List<Image> getIdByTypeTbRefId(String type,String tb_ref_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where type='"+type.trim()+"' and tb_ref_id = '" + tb_ref_id.trim() + "' order by create_date desc ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<Image> getIdByCategoryTbRefId(String category,String tb_ref_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where category='"+category.trim()+"' and tb_ref_id = '" + tb_ref_id.trim() + "' order by create_date desc ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    public List<Image> getByTbRefId(String tb_ref_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where tb_ref_id = '" + tb_ref_id.trim() + "' order by create_date desc ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();            
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<Image> getByTypeTbRefId(String type,String tb_ref_id) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where type='"+type.trim()+"' and tb_ref_id = '" + tb_ref_id.trim() + "' order by create_date desc ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }
    public List<Image> getByTbRefIdCategoryTypeEnable(String tb_ref_id,String category,String type,String enable) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where tb_ref_id = '" + tb_ref_id.trim() + "' and category='" + category.trim() + "' and type='" + type.trim() + "' and enable='" + enable.trim() + "' ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
    public List<Image> getByTbRefIdCategoryType(String tb_ref_id,String category,String type) {
        StringBuffer sql = new StringBuffer();
        sql.append("Select * From image Where tb_ref_id = '" + tb_ref_id.trim() + "' and category='" + category.trim() + "' and type='" + type.trim() + "' ");
        List<Image> list = new ArrayList<Image>();
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql.toString());
            while (rs.next()) {
                Image o = new Image();
                o.setId(rs.getString("ID"));
                o.setName(rs.getString("NAME"));
                o.setTitle(rs.getString("TITLE"));
                o.setDetail(rs.getString("DETAIL"));
                o.setCategory(rs.getString("CATEGORY"));
                o.setType(rs.getString("TYPE"));
                o.setContentType(rs.getString("CONTENT_TYPE"));
                o.setEnable(rs.getString("ENABLE"));
                o.setCreateDate(rs.getString("CREATE_DATE"));
                o.setLastUpdate(rs.getString("LAST_UPDATE"));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setTbRefId(rs.getString("TB_REF_ID"));
                list.add(o);
            }
            rs.close();
            stmt.close();
            conn.close();
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    
////////////////////////////////////////////GET

//////////////////////////////////INSERT
    public boolean insert(Image o) {
        StringBuffer sql = new StringBuffer();
        sql.append("INSERT INTO image(id,name,title,detail,category,type,enable,create_date,last_update,update_by,tb_ref_id,content_type) ");
        sql.append("VALUES (id,?,?,?,?,?,?,?,?,?,?,?)");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getName());
            stmt.setString(2, o.getTitle());
            stmt.setString(3, o.getDetail());
            stmt.setString(4, o.getCategory());
            stmt.setString(5, o.getType());
            stmt.setString(6, o.getEnable());
            stmt.setTimestamp(7,func.getTimestamp());
            stmt.setTimestamp(8,func.getTimestamp());
            stmt.setString(9, o.getUpdateBy());
            stmt.setString(10, o.getTbRefId());
            stmt.setString(11,o.getContentType());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
////////////////////////////////// INSERT

///////////////////////////////// UPDATE
    public boolean update(Image o) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE image ");
        sql.append("SET name=?,title=?,detail=?,category=?,type=?,enable=?,last_update=?,update_by=?,tb_ref_id=?,content_type=? ");
        sql.append("where id='" + o.getId() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getName());
            stmt.setString(2, o.getTitle());
            stmt.setString(3, o.getDetail());
            stmt.setString(4, o.getCategory());
            stmt.setString(5, o.getType());
            stmt.setString(6, o.getEnable());
            stmt.setTimestamp(7,func.getTimestamp());
            stmt.setString(8, o.getUpdateBy());
            stmt.setString(9, o.getTbRefId());
            stmt.setString(10,o.getContentType());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
    public boolean updateTbRefIdById(String id,String tbRefId) {
        StringBuffer sql = new StringBuffer();
        sql.append("UPDATE image ");
        sql.append("SET tb_ref_id=? where id=? ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1,tbRefId.trim());
            stmt.setString(2,id.trim());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
///////////////////////////////// UPDATE

//////////////////////////////////DELETE
    public boolean deleteById(String id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from image where id = '" + id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteByTbRefId(String tb_ref_id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from image where tb_ref_id = '" + tb_ref_id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
    public boolean deleteByTypeTbRefId(String type,String tb_ref_id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from image where type='"+type.trim()+"' and tb_ref_id = '" + tb_ref_id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
    public boolean deleteByCategoryTypeTbRefId(String category,String type,String tb_ref_id) {//delete
        StringBuffer sql = new StringBuffer();
        sql.append("delete from image where category='"+category.trim()+"' and  type='"+type.trim()+"' and tb_ref_id = '" + tb_ref_id.trim() + "' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {//use for web
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            //conn.commit();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
//////////////////////////////////DELETE
}
